package test;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class StudentDaoJDBCImpl implements StudentDao {

	@Override
	public boolean selectSubject(int id,String subject) {
		Connection conn = null;
		PreparedStatement pstat = null;
		PreparedStatement pstat1 = null;
		Statement state = null;
		String sql="update student set "+subject+"=0 where id ="+id+";";
		String sql1="select * from subject where name='"+subject+"';";
		int result = -1;
		ResultSet result1;
		int result2=-1;
		
		try {
			conn = JDBCUtil.getConnection();
			state = conn.createStatement();
			result1 = state.executeQuery(sql1);
			while (result1.next()) {
				if(result1.getInt("optionalNumber")==0) {
					System.out.println("选课已满!");
					return false;
				}
				String sql2="update subject set optionalNumber="+(result1.getInt("optionalNumber")-1)+" where name='"+subject+"';";
				pstat1 = conn.prepareStatement(sql2);
				result2 = pstat1.executeUpdate();
			}
			pstat = conn.prepareStatement(sql);
			result = pstat.executeUpdate();
			
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.realeaseAll(null,pstat, conn);
		}
		return result>0?true:false;
	}

	@Override
	public Student printInfo(int id) {
		Connection conn = null;
		Statement state = null;
		String sql="select * from student where id="+id+";";
		ResultSet result;
		Student student = null;
		ArrayList<Subject> subjectList=new ArrayList<>();
		try {
			conn = JDBCUtil.getConnection();
			state = conn.createStatement();
			result = state.executeQuery(sql);
			while (result.next()) {
				Subject subject1=new Subject("java",result.getDouble("java"),null);
				Subject subject2=new Subject("math",result.getDouble("math"),null);
				Subject subject3=new Subject("sports",result.getDouble("sports"),null);
				subjectList.add(subject1);
				subjectList.add(subject2);
				subjectList.add(subject3);
				student=new Student(id,result.getString("name"),result.getString("grade"),result.getString("gender"),result.getString("birthday"),subjectList);
			}
			
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.realeaseAll(null,state, conn);
		}
		return student;
	}
	
}
